/*
 * Location.java
 *
 * Created on November 6, 2006, 2:28 PM
 */

package com.asaga.tools;

import java.beans.*;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * 
 * Stub implementation of Location Interface
 * @author mmixon
 */
public class Location_DBImpl extends Object implements Location{
    
    private int id;
    private String title;
    private String addr1;
    private String addr2;
    private String city;
    private String state;
    private int zip;
    private String phone;
    private String website;
    private String contactName;
    private String contactEmail;
    private DBPool connectionPool;
    private Connection connection;
    private String message;
   
    
    /**
     * Constructor of Location_StubImpl
     */
    public Location_DBImpl() {
        connectionPool = DBPool.getInstance();
    }

    /**
     * Get Location ID
     * @return returns id of the location
     */
    public int getId() {
        return id;
    }

    /**
     * Set Location ID
     * @param id set's the location id
     */
    public void setId(int id) {
        this.id = id;
    }

    /**
     * Get Title Method
     * @return Returns the title of the location
     */
    public String getTitle() {
        return title;
    }

    /**
     * Set Title Method
     * @param title the title to be set
     */
    public void setTitle(String title) {
        this.title = title;
    }

    /**
     * Get Addr1
     * @return Returns the first address line.
     */
    public String getAddr1() {
        return addr1;
    }

    /**
     * Set Addr1
     * @param addr1 Sets the first address line.
     */
    public void setAddr1(String addr1) {
        this.addr1 = addr1;
    }

    /**
     * Get Addr2
     * @return returns the second address line
     */
    public String getAddr2() {
        return addr2;
    }

    /**
     * Set Addr2
     * @param addr2 sets the secon address line.
     */
    public void setAddr2(String addr2) {
        this.addr2 = addr2;
    }

    /**
     * Get City Method
     * @return returns city
     */
    public String getCity() {
        return city;
    }

    /**
     * Set City Method
     * @param city sets the city to this parameter
     */
    public void setCity(String city) {
        this.city = city;
    }

    /**
     * Get State Method
     * @return returns state attribute
     */
    public String getState() {
        return state;
    }

    /**
     * Set State Method
     * @param state sets the state attribute.
     */
    public void setState(String state) {
        this.state = state;
    }

    /**
     * Get Zip Method
     * @return returns the zip of the location.
     */
    public int getZip() {
        return zip;
    }

    /**
     * Set Zip Method
     * @param zip sets the zip attribute.
     */
    public void setZip(int zip) {
        this.zip = zip;
    }

    /**
     * Get Phone Method
     * @return method returns the phone number of the location.
     */
    public String getPhone() {
        return phone;
    }

    /**
     * Set Phone Method
     * @param phone sets the phone attribute.
     */
    public void setPhone(String phone) {
        this.phone = phone;
    }

    /**
     * Get Website Method
     * @return returns the website attribute.
     */
    public String getWebsite() {
        return website;
    }

    /**
     * Set Website Method
     * @param website sets the website attribute.
     */
    public void setWebsite(String website) {
        this.website = website;
    }

    /**
     * Get Contact Name Method
     * @return returns the contact name
     */
    public String getContactName() {
        return contactName;
    }
    
    /**
     * Set New Id Method
     *  Method that sets the new id to the location object
     *  once it has been added to the system
     */
    public void setNewId(){
        ResultSet rs = null;
        connection = connectionPool.getConnection();
        String locationIdQuery = "select locationID from location l where " +
                "l.title = '" + title + "' AND " +
                "l.zip = " + zip + " AND " +
                "l.city = '" + city + "' AND " +
                "l.state = '" + state + "'";
        try{
            java.sql.Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(locationIdQuery);
            if(rs.next())
                id = rs.getInt("locationID");
        }
        catch(SQLException se){
            message = "SQL Error:  " + se.getMessage();
        }
    }

    /**
     * Set Contact Name method
     * @param contactName sets contactName attribute.
     */
    public void setContactName(String contactName) {
        this.contactName = contactName;
    }

    /**
     * Get Contact Email Method
     * @return returns the contactEmail attribute
     */
    public String getContactEmail() {
        return contactEmail;
    }
    
    /**
     * Get Locations by State Method
     * @param state the state of which to find locations
     * 
     * @return -- returns list of locations
     */
    public ResultSet getLocationsByState(String state){
        ResultSet locations = null;
        String locationSQL = "select locationID, title, city, state from location l where" +
                " l.state = '" + state + "' order by l.title";
        connection = connectionPool.getConnection();

            try{
                java.sql.Statement stmt = connection.createStatement();
                locations = stmt.executeQuery(locationSQL);
            }
            catch(SQLException se){
                message = "SQL Error: " + se.getMessage() + se.getSQLState();
            }

        return locations;
    }

    /**
     * Set Contact Email Method
     * @param contactEmail sets the contactEmail attribute
     */
    public void setContactEmail(String contactEmail) {
        this.contactEmail = contactEmail;
    }
    
    /**
     * Set Location By ID Method
     * @param theid -- id of which to set the location by
     */
    public void setLocationById(int theid){
        String locationSQL = "select * from location where locationID = " + theid;
        connection = connectionPool.getConnection();
        try{
            java.sql.Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(locationSQL);
            if(rs.next()){
                id = rs.getInt("locationID");
                title = rs.getString("title");
                addr1 = rs.getString("addr1");
                addr2 = rs.getString("addr2");
                city = rs.getString("city");
                state = rs.getString("state");
                zip = rs.getInt("zip");
                website = rs.getString("website");
                contactName = rs.getString("contactName");
                contactEmail = rs.getString("contactEmail");
            }
            else{
                message = "No Location Found.";
            }
        }
        catch(SQLException se){
            //stub
            //write to log file
        }
        
    }

    /**
     * Add Location Method
     */
    public void addLocation(){
        String addLocationSQL = "insert into location (title, addr1, addr2, city, state, zip, phone, website, " +
                "contactName, contactEmail) values (?,?,?,?,?,?,?,?,?,?)";
        connection = connectionPool.getConnection();
        try{
            PreparedStatement ps = connection.prepareStatement(addLocationSQL);
            ps.setString(1, SQLUtil.encode(title));
            ps.setString(2, SQLUtil.encode(addr1));
            ps.setString(3, SQLUtil.encode(addr2));
            ps.setString(4, SQLUtil.encode(city));
            ps.setString(5, SQLUtil.encode(state));
            ps.setInt(6, zip);
            ps.setString(7, SQLUtil.encode(phone));
            ps.setString(8, SQLUtil.encode(website));
            ps.setString(9, SQLUtil.encode(contactName));
            ps.setString(10, SQLUtil.encode(contactEmail));
            ps.executeUpdate();
        }
        catch(SQLException se){
            setMessage("SQL Error: " + se.getMessage() + se.getSQLState());
        }
        connectionPool.freeConnection(connection);
        
    }

    /**
     * Get Venu Method
     * @return returns the venue attribute
     */

    public String getMessage() {
        return message;
    }

    /**
     * Set Message Method
     * @param message -- sets message attribute of location object
     */
    public void setMessage(String message) {
        this.message = message;
    }
}
